OpenEdge Data Management:<br />SQL Reference ALTER TABLE

Release 10.1A: OpenEdge Data Management:
SQL Reference

ALTER TABLE

The ALTER TABLE statement can be used to:

Change the name of a table.

Change the name of a column within a table.

Add a column to a table.

Set Progress 4GL table and column attributes.

Syntax
ALTER TABLE [ owner_name.]table_name  
{ADD column-definition  
 |SET progress_table_attribute value 
 |ALTER [ COLUMN ]column_name { SET DEFAULT value  
                                | DROP DEFAULT  
                                | SET [NOT] NULL 
                                |SET progress_column_attribute value} 
                                [COLLATE case_insensitive | case_sensitive] 
 |DROP COLUMN column_name { CASCADE | RESTRICT } 
 |ADD { primary_key_definition | foreign_key_definition | 
         uniqueness_constraint | check_constraint } 
|ADD CONSTRAINT constraint_name 
|DROP CONSTRAINT constraint_name [ CASCADE | RESTRICT] 
|ALTER INDEX index_name SET progress_index_attribute value 
|RENAME {table_name TO new_table_name  
         |COLUMN column_name TO new_column_name  
         |INDEX index_name TO new_index_name } 
}; 
Note: See the "Syntax for 4GL Attributes" section for a list of Progress 4GL table and column attributes.
Example

In the following example, the ALTER TABLE statement is used to change the name of a table. In the example, the table named customer changes to Customers:
ALTER TABLE customer RENAME TO Customers; 
Example

In this example, the ALTER TABLE statement is used to change the name of a column within a table and the column named Address changes to Street:
ALTER TABLE customer RENAME Address TO Street; 
Example

In this example, table customer adds the column Region:
ALTER TABLE customer ADD COLUMN Region; 
Example

In this example, ALTER TABLE adds a Progress 4GL description to a table and changes the 4GL default data access index of the table:
ALTER TABLE pub.customer SET PRO_DESCRIPTION ’Sports 2000 Customers’; 
ALTER TABLE pub.customer SET PRO_DEFAULT_INDEX CustNumIdx; 
Example

In this example, ALTER TABLE RENAME INDEX is used to change an index named CustNum to CustomerNumberIndex:
ALTER TABLE Customers RENAME INDEX CustNum to CustomerNumberIndex; 
Note

The ALTER TABLE statement enables you to change the names of tables or columns or to add columns while your database is online servicing other requests. Other changes performed by ALTER TABLE must occur offline.

Authorization

Must have the DBA privilege, ownership of the table, or all the specified privileges on the table.

Related statements

ADD TABLE, DROP TABLE